﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Security.Cryptography;

namespace CSEPT
{
    public class DAL
    {
        public DAL()
        {

        }
        private SqlConnection getConnection()
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);
            conn.Open();
            return conn;
        }
        public void SetTransaction(Transaction Trans)
        {
            using (SqlConnection conn = getConnection())
            {
                SqlCommand cmd = new SqlCommand("CSEPT_SP_AddTransaction", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@UserID", Trans.Usr.ID);
                cmd.Parameters.AddWithValue("@type", Trans.Type);
                cmd.Parameters.AddWithValue("@Amount", Trans.Amount);
                cmd.Parameters.AddWithValue("@Desc", Trans.Description);
                cmd.ExecuteNonQuery();
            }
        }
        public Bib GetBib(User User, Event Event){
            if((User.ID) == 0 || (Event.ID) == 0)
                Log.Error("Cannot get the registration if User.ID or Event.ID is null", "CSEPT.DAL.GetBib");
            return GetBib((int)User.ID, (int)Event.ID);
            
        }
        public Bib GetBib(int UserID, int EventID)
        {
            using (SqlConnection conn = getConnection())
            {
                SqlCommand cmd = new SqlCommand("CSEPT_SP_GetBib", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@UserID", UserID);
                cmd.Parameters.AddWithValue("@EventID", EventID);
                SqlDataReader dr = cmd.ExecuteReader();
                Bib TempBib;
                if (dr.Read())
                {
                    TempBib = new Bib(dr["ID"] as int? ?? 0, new User(dr["UserID"] as int? ?? 0), new Event(dr["EventID"] as int? ?? 0), dr["BibNum"] as string, false);
                }
                else
                {
                    TempBib = new Bib();
                }
                TempBib.RegisteredEvent = GetEvent(EventID);
                TempBib.RegisteredUser = GetUser(UserID);
                return TempBib;
            }
        }

        private void UpdateBib(Bib bib, bool isDeleted)
        {
            using (SqlConnection conn = getConnection())
            {
                SqlCommand cmd = new SqlCommand("CSEPT_SP_UpdateBib", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@BibID", bib.ID);
                cmd.Parameters.AddWithValue("@UserID", bib.RegisteredUser.ID);
                cmd.Parameters.AddWithValue("@EventID", bib.RegisteredEvent.ID);
                cmd.Parameters.AddWithValue("@BibNumber", bib.BibNumber);
                cmd.Parameters.AddWithValue("@isDeleted", isDeleted);
                cmd.ExecuteNonQuery();
            }
        }
        public void UpdateBib(Bib bib)
        {
            UpdateBib(bib, false);
        }
        public void DeleteBib(Bib bib)
        {
            UpdateBib(bib, true);
        }
        public void DeleteBib(int BibID)
        {
            UpdateBib(new Bib(BibID), true);
        }
        public List<Photo> GetTaggedPhotos(User Usr)
        {
            return GetPhotos(new Event(), Usr, new Photo(), new User());
        }
        public Photo GetPhoto(int PhotoID)
        {
            return GetPhotos(new Event(), new User(), new Photo(PhotoID), new User()).First();
        }
        public Photo GetRandomPhoto(){
            return GetPhotos(new Event(), new User(), new Photo(), new User()).First();
        }
        public List<Photo> GetEventPhotos(Event Evnt)
        {
            return GetPhotos(Evnt, new User(), new Photo(), new User());
        }
        private List<Photo> GetPhotos(Event Evnt, User Usr, Photo Phto, User Photographer){
            List<Photo> returnList = new List<Photo>();
            using(SqlConnection conn = getConnection())
            {
                SqlCommand cmd = new SqlCommand("CSEPT_SP_GetPhotos", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@EventID", Evnt.ID);
                cmd.Parameters.AddWithValue("@UserID",Usr.ID);
                cmd.Parameters.AddWithValue("@PhotoID",Phto.ID);
                cmd.Parameters.AddWithValue("@PhotoOwnerID",Photographer.ID);
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    returnList.Add(new Photo(dr["ID"] as int? ?? 0, dr["EventID"] as int? ?? 0, dr["Location"] as string, dr["PhotoOwnerID"] as int? ?? 0, dr["isTagged"] as bool? ?? false));
                }
            }
            return returnList;
        }
        public int UpdatePhoto(Photo Phto){
            using (SqlConnection conn = getConnection())
            {
                SqlCommand cmd = new SqlCommand("CSEPT_SP_UpdatePhoto", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@EventID", Phto.PhotoEvent.ID);
                cmd.Parameters.AddWithValue("@FileLocation", Phto.Location);
                cmd.Parameters.AddWithValue("@Owner", Phto.Photographer.ID);
                cmd.Parameters.AddWithValue("@PhotoID", Phto.ID);
                cmd.Parameters.Add("@ReturnVal", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                cmd.ExecuteNonQuery();
                return Convert.ToInt32(cmd.Parameters["@ReturnVal"].Value);
            }
        }

        public User GetUser(int UsrID){
            using (SqlConnection conn = getConnection())
            {
                SqlCommand cmd = new SqlCommand("CSEPT_SP_GetUser", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@UserID", UsrID);
                SqlDataReader dr = cmd.ExecuteReader();
                try
                {
                    dr.Read();
                    User Usr = new User(dr["ID"] as int? ?? 0);
                    Usr.UserName = dr["UserName"] as string;
                    Usr.SecurityNumber = dr["Security"] as int? ?? 0;
                    Usr.Email = dr["Email"] as string;
                    Usr.Credits = dr["Credits"] as int? ?? 0;
                    if (Usr.ID == 0)
                    {
                        throw new Exception("User not found");
                    }
                    return Usr;
                }
                catch (Exception)
                {
                        throw new Exception("User not found");
                }
                return new User();
            }
        }
        public void UpdateUser(User Usr)
        {
            UpdateUser(Usr, null, false);
        }
        public void CreateUser(User Usr, string Password)
        {
            Usr.SecurityNumber = 0;
            UpdateUser(Usr, Password, false);
        }
        public void UpdateUserPassword(User Usr, string Password)
        {
            UpdateUser(Usr, Password, false);
        }
        public void DeleteUser(User Usr)
        {
            UpdateUser(Usr, null, true);
        }
        private void UpdateUser(User Usr, string Password, bool isDeleted){
            using (SqlConnection conn = getConnection())
            {
                SqlCommand cmd = new SqlCommand("CSEPT_SP_UpdateUser", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@UserID", Usr.ID);
                cmd.Parameters.AddWithValue("@UserName", Usr.UserName);
                cmd.Parameters.AddWithValue("@Password", Password);
                cmd.Parameters.AddWithValue("@SecurityNum", Usr.SecurityNumber);
                cmd.Parameters.AddWithValue("@Email", Usr.Email);
                cmd.Parameters.AddWithValue("@isDeleted", isDeleted);
                cmd.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                cmd.ExecuteNonQuery();
                if (Convert.ToInt32(cmd.Parameters["@Return"].Value)  == 1)
                {
                    throw new Exception("Username unavailable");
                }
            }
        }
        public List<Event> GetEvents()
        {
            return GetEvents(0, 0);
        }
        public List<Event> GetEvents(User Usr){
            return GetEvents(Usr.ID, 0);
        }
        public Event GetEvent(Event Evnt)
        {
            return GetEvent((int)Evnt.ID);
        }
        public Event GetEvent(int EventID)
        {
            return GetEvents(0, EventID).First();
        }
        public List<Event> GetEvents(int UserID, int EventID){
            List<Event> ReturnList = new List<Event>();
            using (SqlConnection conn = getConnection())
            {
                SqlCommand cmd = new SqlCommand("CSEPT_SP_GetEvents", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@UserID", UserID);
                cmd.Parameters.AddWithValue("@EventID", EventID);
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    ReturnList.Add(new Event(dr["ID"] as int? ?? 0, dr["Title"] as string, dr["StartDate"] as DateTime? ?? DateTime.Now, dr["EndDate"] as DateTime? ?? DateTime.Now, dr["Description"] as string, dr["EventOwnerID"] as int? ?? 0));
                    try
                    {
                        ReturnList.Last().Thumbnail = GetEventPhotos(ReturnList.Last()).First() ?? new Photo();
                    }
                    catch (Exception)
                    {
                        ReturnList.Last().Thumbnail = new Photo();
                    }
                }
            }
            return ReturnList;
        }
        public void UpdateEvent(Event Evnt)
        {
            UpdateEvent(Evnt, false);
        }
        public void DeleteEvent(int EvntID)
        {
            DeleteEvent(new Event(EvntID));
        }
        public void DeleteEvent(Event Evnt)
        {
            UpdateEvent(Evnt, true);
        }
        private void UpdateEvent(Event Evnt, bool isDeleted){
            using (SqlConnection conn = getConnection())
            {
                SqlCommand cmd = new SqlCommand("CSEPT_SP_UpdateEvent", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ID", Evnt.ID);
                cmd.Parameters.AddWithValue("@Title", Evnt.Title);
                cmd.Parameters.AddWithValue("@StartDate", Evnt.StartDate);
                cmd.Parameters.AddWithValue("@EndDate", Evnt.EndDate);
                cmd.Parameters.AddWithValue("@Description", Evnt.Description);
                cmd.Parameters.AddWithValue("@User", Evnt.Owner.ID);
                cmd.Parameters.AddWithValue("@isDeleted", isDeleted);
                cmd.ExecuteNonQuery();                
            }

        }
        public void UpdateTag(Tag tg){
            using (SqlConnection conn = getConnection())
            {
                SqlCommand cmd = new SqlCommand("CSEPT_SP_UpdateTags", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@EventID", tg.TagEvent.ID);
                cmd.Parameters.AddWithValue("@PhotoID", tg.TaggedPhoto.ID);
                cmd.Parameters.AddWithValue("@UserID", tg.TaggedBy.ID);
                cmd.Parameters.AddWithValue("@BibNumber", tg.BibNum);
                cmd.Parameters.AddWithValue("@TagID", tg.ID);
                cmd.ExecuteNonQuery();
            }
        }
        public User AttemptLogin(User Usr, string password)
        {
            Usr = GetUser((int)Usr.ID);
            return AttemptLogin(Usr.UserName, password);
        }
        public User AttemptLogin(string UserName, string Password)
        {
            using (SqlConnection conn = getConnection())
            {
                SqlCommand cmd = new SqlCommand("CSEPT_SP_TestPassword", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@UserName", UserName);
                cmd.Parameters.AddWithValue("@Password", Password);
                cmd.Parameters.Add("@UserID", SqlDbType.Int).Direction = ParameterDirection.Output;
                cmd.ExecuteNonQuery();
                int UserID = Convert.ToInt32(cmd.Parameters["@UserID"].Value);
                if (UserID == 0)
                {
                    throw new Exception("Invalid Login Credentials");
                }
                return GetUser(UserID);
            }
        }
        public string EncryptPassword(string Password)
        {
            return  "superawesomeenc" + Password + "superawesomeenc";
        }
    }
}
